﻿create proc locdocgia
as
begin 
begin tran
set tran isolation level serializable
declare @madocgia smallint,@tong int
declare cur cursor dynamic for select ma_docgia from DocGia where year(NgaySinh) >'1991'
open cur
set @tong = (select count(distinct ma_docgia) from DocGia where year(NgaySinh) >'1991')
print N'Tong so nguoi' + cast(@tong as char(3))
print N'Danh sach'
print'-----------------------------------'
fetch next from cur into @madocgia
while(@@FETCH_STATUS=0)
begin
	print @madocgia
	waitfor delay'00:00:05'
	fetch next from cur into @madocgia
end
	close cur
	deallocate cur
commit tran
end

---------------------
create proc sp_ThemNguoiLon @ho nvarchar(15), @tenlot nvarchar(1), @ten nvarchar(15), @ngaysinh smalldatetime,--thang/ngay/nam
												@sonha nvarchar(15), @duong nvarchar(63), @quan nvarchar(2), @dienthoai nvarchar(13)
as
begin
		if(year(getdate()) -year(@ngaysinh) < 18)
			begin
					print N'Khong đu tuoi!!!'
					return;
			end
		else
			begin
					declare @temp_1 int, @flag int
					set @flag = 1
					declare @cur_1 cursor
					set @cur_1 = cursor for( 
														select dg.ma_docgia
														from DocGia dg
													)
					open @cur_1
						fetch next from @cur_1 into @temp_1
						while @@fetch_status = 0
								begin
										if(@temp_1 - @flag >0)
											begin
												break
											end
										set @flag = @flag + 1;
										fetch next from @cur_1 into @temp_1
								end
						--quy dinh: th? d?c gi? s? d?ng 12 tháng
						declare @han_sd smalldatetime
						set @han_sd = dateadd(yy,1,getdate())
						
						insert into DocGia(ma_docgia,ho,tenlot,ten,NgaySinh)values(@flag,@ho,@tenlot,@ten,@ngaysinh)
						insert into NguoiLon(ma_docgia,sonha,duong,quan,dienthoai,han_sd) values(@flag,@sonha,@duong,@quan,@dienthoai,@han_sd)
						print N'Them thanh cong!!!'
					close @cur_1
					deallocate @cur_1
			end
end